Query plan

The SHOW PLAN SQL statement and the "plan": N JSON interface option display the query execution plan. The plan is generated and stored during the actual execution, so in the case of SQL, profiling must be enabled in the current session before running that statement. This can be done with a SET profiling=1 statement.

Two items are returned in SQL mode:

  • transformed_tree, which displays the full-text query decomposition.
  • enabled_indexes, which provides information about effective secondary indexes.

To view the query execution plan in a JSON query, add "plan": N to the query. The result will appear as a plan property in the result set. N can be one of the following:

  • 1 - Displays only the textual plan of the root node, similar to the one returned in the SHOW PLAN SQL query. This is the most compact form.
  • 2 - Displays only the JSON object plan, useful for processing.
  • 3 - Displays a JSON object with a textual description of every node. Note that the description for child nodes is also present and repeats part of the parent's description, which makes the whole representation quite large.
‹›
  • SQL
  • JSON
📋
set profiling=1;

select * from hn_small where match('dog|cat') limit 0;

show plan;
‹›
Response
*************************** 1. row ***************************
Variable: transformed_tree
   Value: OR(
  AND(KEYWORD(dog, querypos=1)),
  AND(KEYWORD(cat, querypos=2)))
*************************** 2. row ***************************
Variable: enabled_indexes
   Value:
2 rows in set (0.00 sec)

In some cases, the evaluated query tree can be quite different from the original one due to expansions and other transformations.

‹›
  • SQL
  • JSON
📋
SET profiling=1;

SELECT id FROM forum WHERE MATCH('@title way* @content hey') LIMIT 1;

SHOW PLAN;
‹›
Response
Query OK, 0 rows affected (0.00 sec)

+--------+
| id     |
+--------+
| 711651 |
+--------+
1 row in set (0.04 sec)

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable         | Value                                                                                                                                                                                                                                                                                                                                                                                                                   |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| transformed_tree | AND(
  OR(
    OR(
      AND(fields=(title), KEYWORD(wayne, querypos=1, expanded)),
      OR(
        AND(fields=(title), KEYWORD(ways, querypos=1, expanded)),
        AND(fields=(title), KEYWORD(wayyy, querypos=1, expanded)))),
    AND(fields=(title), KEYWORD(way, querypos=1, expanded)),
    OR(fields=(title), KEYWORD(way*, querypos=1, expanded))),
  AND(fields=(content), KEYWORD(hey, querypos=2))) |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See also EXPLAIN QUERY. It displays the execution tree of a full-text query without actually executing the query. Note that when using SHOW PLAN after a query to a real-time table, the result will be based on a random disk/RAM chunk. Therefore, if you have recently modified the table's tokenization settings, or if the chunks vary significantly in terms of dictionaries, etc., you might not get the result you are expecting. Take this into account and consider using EXPLAIN QUERY as well.

JSON result set notes

query property contains the transformed full-text query tree. Each node contains:

  • type: node type. Can be AND, OR, PHRASE, KEYWORD, etc.
  • description: query subtree for this node shown as a string (in SHOW PLAN format).
  • children: child nodes, if any.
  • max_field_pos: maximum position within a field.
  • word: transformed keyword. Keyword nodes only.
  • querypos: position of this keyword in a query. Keyword nodes only.
  • excluded: keyword excluded from query. Keyword nodes only.
  • expanded: keyword added by prefix expansion. Keyword nodes only.
  • field_start: keyword must occur at the very start of the field. Keyword nodes only.
  • field_end: keyword must occur at the very end of the field. Keyword nodes only.
  • boost: keyword IDF will be multiplied by this. Keyword nodes only.

Dot format for SHOW PLAN

SHOW PLAN format=dot allows returning the full-text query execution tree in a hierarchical format suitable for visualization by existing tools, such as https://dreampuf.github.io/GraphvizOnline:

MySQL [(none)]> show plan option format=dot\G
*************************** 1. row ***************************
Variable: transformed_tree
   Value: digraph "transformed_tree"
{

0 [shape=record,style=filled,bgcolor="lightgrey" label="AND"]
0 -> 1
1 [shape=record,style=filled,bgcolor="lightgrey" label="AND"]
1 -> 2
2 [shape=record label="i | { querypos=1 }"]
0 -> 3
3 [shape=record,style=filled,bgcolor="lightgrey" label="AND"]
3 -> 4
4 [shape=record label="me | { querypos=2 }"]
}

SHOW PLAN graphviz example

⪢ Table settings and status

SHOW TABLE STATUS

SHOW TABLE STATUS is an SQL statement that displays various per-table statistics.

The syntax is:

SHOW TABLE index_name STATUS

Depending on index type, displayed statistic includes different set of rows:

  • template: index_type.
  • distributed: index_type, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • percolate: index_type, stored_queries, ram_bytes, disk_bytes, max_stack_need, average_stack_base, desired_thread_stack, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • plain: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • rt: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, ram_chunk, ram_chunk_segments_count, disk_chunks, mem_limit, mem_limit_rate, ram_bytes_retired, locked, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.

Here is the meaning of these values:

  • index_type: currently one of disk, rt, percolate, template, and distributed.
  • indexed_documents: number of indexed documents.
  • indexed_bytes: overall size of indexed text. Notice, this value is not strict, since in full-text index that is impossible to strictly recover back stored text to measure it.
  • stored_queries: number of percolate queries, stored in the table.
  • field_tokens_XXX: optional, total per-field lengths (in tokens) across the entire table (used internally for BM25A and BM25F ranking functions). Only available for tables built with index_field_lengths=1.
  • total_tokens: optional, overall sum of all field_tokens_XXX.
  • ram_bytes: total RAM occupied by table.
  • disk_bytes: total disk space, occupied by table.
  • disk_mapped: total size of file mappings.
  • disk_mapped_cached: total size of file mappings actually cached in RAM.
  • disk_mapped_doclists and disk_mapped_cached_doclists: portion of total and cached mappings belonging to document lists.
  • disk_mapped_hitlists and disk_mapped_cached_hitlists: portion of total and cached mappings belonging to hit lists. Doclists and hitlists values are shown separately since they're typically large (e.g., about 90% of the whole table's size).
  • killed_documents and killed_rate: the first indicates the number of deleted documents and the rate of deleted/indexed. Technically, deleting a document means suppressing it in search output, but it still physically exists in the table and will only be purged after merging/optimizing the table.
  • ram_chunk: size of the RAM chunk of real-time or percolate table.
  • ram_chunk_segments_count: RAM chunk is internally composed of segments, typically no more than 32. This line shows the current count.
  • disk_chunks: number of disk chunks in the real-time table.
  • mem_limit: actual value of rt_mem_limit for the table.
  • mem_limit_rate: the rate at which the RAM chunk will be flushed as a disk chunk, e.g., if rt_mem_limit is 128M and the rate is 50%, a new disk chunk will be saved when the RAM chunk exceeds 64M.
  • ram_bytes_retired: represents the size of garbage in RAM chunks (e.g., deleted or replaced documents not yet permanently removed).
  • locked: a value greater than 0 indicates that the table is currently locked by FREEZE. The number represents how many times the table has been frozen. For instance, a table might be frozen by manticore-backup and then frozen again by replication. It should only be completely unfrozen when no other process requires it to be frozen.
  • max_stack_need: stack space we need to calculate most complex from the stored percolate queries. That is dynamic value, depends on build details as compiler, optimization, hardware, etc.
  • average_stack_base: stack space which is usually occupied on start of calculation of percolate query.
  • desired_thread_stack: sum of above values, rounded up to 128 bytes edge. If this value is greater than thread_stack, you may not execute call pq over this table, as some stored queries will fail. Default thread_stack value is 1M (which is 1048576); other values should be configured.
  • tid and tid_saved: represent the state of saving the table. tid increases with each change (transaction). tid_saved shows the max tid of the state saved in a RAM chunk in <table>.ram file. When the numbers differ, some changes exist only in RAM and are also backed by binlog (if enabled). Performing FLUSH TABLE or scheduling periodic flushing saves these changes. After flushing, the binlog is cleared, and tid_saved represents the new actual state.
  • query_time_*, exact_query_time_*: query execution time statistics for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
  • found_rows_*: statistics of rows found by queries; provided for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
‹›
  • SQL
  • PHP
  • Python
  • Javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
mysql> SHOW TABLE statistic STATUS;
‹›
Response
+-----------------------------+--------------------------------------------------------------------------+
| Variable_name               | Value                                                                    |
+-----------------------------+--------------------------------------------------------------------------+
| index_type                  | rt                                                                       |
| indexed_documents           | 146000                                                                   |
| indexed_bytes               | 149504000                                                                |
| ram_bytes                   | 87674788                                                                 |
| disk_bytes                  | 1762811                                                                  |
| disk_mapped                 | 794147                                                                   |
| disk_mapped_cached          | 802816                                                                   |
| disk_mapped_doclists        | 0                                                                        |
| disk_mapped_cached_doclists | 0                                                                        |
| disk_mapped_hitlists        | 0                                                                        |
| disk_mapped_cached_hitlists | 0                                                                        |
| killed_documents            | 0                                                                        |
| killed_rate                 | 0.00%                                                                    |
| ram_chunk                   | 86865484                                                                 |
| ram_chunk_segments_count    | 24                                                                       |
| disk_chunks                 | 1                                                                        |
| mem_limit                   | 134217728                                                                |
| mem_limit_rate              | 95.00%                                                                   |
| ram_bytes_retired           | 0                                                                        |
| locked                      | 0                                                                        |
| tid                         | 0                                                                        |
| tid_saved                   | 0                                                                        |
| query_time_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_1min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_5min             | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_15min            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_total            | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
+-----------------------------+--------------------------------------------------------------------------+
29 rows in set (0.00 sec)